package top.wuyang.study.javawebjdbc.dao;

import top.wuyang.study.javawebjdbc.entity.TbMovie;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class TbMovieDAO {

    public static int insert(TbMovie tbMovie) throws Exception {
        Connection conn = DBHelper.getConnection();
        // 数据库预备语句最好不要带分号
        String sql = "insert into tb_movie (movie, info, score) values (?, ?, ?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, tbMovie.getMovie());
        ps.setString(2, tbMovie.getInfo());
        ps.setInt(3,tbMovie.getScore());
        int result = ps.executeUpdate();
        conn.close();
        return result;
    }

    public static List<TbMovie> query() throws Exception {
        List<TbMovie> list = new ArrayList<>();
        Connection conn = DBHelper.getConnection();
        String sql = "select * from tb_movie";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            TbMovie tbMovie = new TbMovie();
            tbMovie.setId(rs.getInt("id"));
            tbMovie.setMovie(rs.getString("movie"));
            tbMovie.setInfo(rs.getString("info"));
            tbMovie.setScore(rs.getInt("score"));
            tbMovie.setCreated(rs.getTimestamp("created"));
            list.add(tbMovie);
        }
        conn.close();
        return list;
    }

    public static int delete(Integer id) throws Exception {
        Connection conn = DBHelper.getConnection();
        String sql = "delete from tb_movie where id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        int result = ps.executeUpdate();
        conn.close();
        return result;
    }


}
